データ統合プラットフォーム Xplenty を使って、2020 年の沖縄県の潮位データを整形してから BigQuery にロードしてみた

「カスタマーストーリー」を支えるツール特集

#ビッグデータ

#ビジネス・アナリティクス

#データ分析

#Xplenty

#Google BigQuery

みかみ

2020.06.12

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、みかみです。

データアナリティクス事業本部に所属しています。業務ではデータ分析基盤を構築することが多々あります。データを抽出（ Extract ）し、分析に必要な形に変換・加工（ Transform ）を行い、データウェアハウスに格納（ Load ）する、ETL 処理がデータ分析基盤の主なお仕事です。

Xplenty では GUI 操作で直感的操作で簡単に ETL 処理が実行できるということなので、本当に簡単に実行できるのか確認してみました。

全てのデータソースを統合するためのリーディングデータ統合プラットフォーム｜ Xplenty

やりたいこと

Xplenty をさわってみたい
2020 年の沖縄県の潮位データを BigQuery にロードしたい
BigQuery にロードした潮位データを参照して、明日（ 2020/06/13 ）の最適な釣り時刻（満潮時刻）を確認したい
潮位データから、2020年の釣り日和（満潮時の潮位が高い日）はいつか知りたい

前提

Xplenty の実行環境は準備済みです。

また、AWS と GCP のアカウントおよび S3 と BigQuery などのリソースも準備済みです。

ロードするデータは気象庁の Web ページからいただいてきた、沖縄県の 2020 年の潮位表テキストデータです。AWS S3 に配置しました。

潮位表沖縄（OKINAWA）｜気象庁

テキストデータの中身は以下のような感じです。

123 93 64 43 33 38 55 81109135152157152137118101 89 87 9410912614114814520 1 1ZO11 0157221414999999999999999 411 331643 8699999999999999
132112 88 66 51 47 55 72 94118138150153146132115 99 90 88 9310511913013620 1 2ZO1145153231213699999999999999 450 471744 8799999999999999
133123107 88 72 63 62 70 85105125140149150143129113 97 87 83 87 9610811920 1 3ZO1235151999999999999999999999 536 611859 8399999999999999
126126120108 95 82 75 75 82 95111128142150150142128110 93 80 74 75 84 9720 1 4ZO 032127133015199999999999999 634 742017 7399999999999999
110120125123115104 93 86 84 89100115131145153152143126105 84 68 60 61 7120 1 5ZO 212125142615499999999999999 748 842121 5999999999999999
（省略）

フォーマットは以下です。

　毎時潮位データ：　１～　７２カラム　３桁×２４時間（０時から２３時）
　年月日：７３～　７８カラム　２桁×３
　地点記号：７９～　８０カラム　２桁英数字記号
　満潮時刻・潮位：８１～１０８カラム　時刻４桁（時分）、潮位３桁（ｃｍ）
　干潮時刻・潮位：１０９～１３６カラム　時刻４桁（時分）、潮位３桁（ｃｍ）
　※ 満（干）潮が予測されない場合、満（干）潮時刻を「9999」、潮位を「999」としています。

抜粋：潮位表（PDF版・テキストデータ版）フォーマット｜気象庁

AWS S3 と BigQuery の connection を作成

Xplenty で、まずは画面上部の「Get Started」プルダウンに従って、connection を作成します。

「Object Stores」の中から「Amazon S3」を選択し、connection 名と接続先の S3 のリージョン、アクセスキー情報を入力します。

「Test Connection」で指定した S3 に接続できることが確認できたので、「Create connection」をクリック。

次にデータロード先の BigQuery の connection を作成します。

「Object Stores」の中から「Google BigQuery」を選択し、connection 名と GCP のプロジェクト ID、BigQuery のデータセット ID とリージョンを入力します。

こちらも問題なく接続できることが確認できたので、「Create connection」で connection を作成しました。

なお、BigQuery の connection を作成するには、あらかじめ BigQuery 側でデータセットに Xplenty からのアクセス権を追加しておく必要があります。

以下のエントリでも詳しい手順を記載しているのでご参照ください。

XplentyからBigQueryに接続設定を行ってみるXplentyからBigQueryに接続設定を行ってみる｜ Developers.IO

package を作成

続いて package を作成します。

package 名とタイプを入力して「Create package」をクリックするだけです。

タイプにはプルダウンから「Dataflow」と「Workflow」を選択できます。「Workflow」ではタスクの依存関係を定義できるとのことですが、今回はソースデータを取得して BigQuery にロードするデータフローを作りたいため、「Dataflow」を選択しました。

Xplenty terminology ｜ Xplenty ドキュメント

「Template」は「Blank」で何も指定していないため、まずはまっさらな GUI 画面が表示されます。

「Add Component」から処理を追加していきます。

ソースデータのコンポーネントを追加

ソースデータと各種 Transform 処理、データ格納先のカテゴリに分かれていて、直感的に分かりやすい GUI です。

ソースデータは S3 から取得するので、まずは「Sources」列の中から「File Storage」を選択します。

編集画面に追加された file_storage のアイコンをクリックして、connection を選択して

バケット名、ソースファイルパス、レコードのデリミタとファイルフォーマット、どのファイルを対象とするかを指定します。

問題なく設定できていれば、ファイルからフィールドを自動検出してデータのプレビューを表示してくれます。

データ整形用のコンポーネントを追加

続いて、ファイルデータを整形するためのコンポーネントを追加して、先ほどの File Storage コンポーネントとつなぎます。

Select コンポーネントをクリックして、文字列をデータフォーマットに従って項目ごとに分割するように編集していきます。

Select コンポーネントの EXPRESSION EDITOR で、データ型のキャストやパース、日時項目のフォーマット変換や文字列操作の関数を使用することができます。マウスオーバーで各関数の処理内容や引数の説明が表示されるので、関数名を知らなくても欲しい処理を見つけることができますし、関数は TRIM や CONCAT など、SQL やプログラム言語などの経験のある方なら直感的に分かりやすい名前です。

Xplenty入門 Part2: 変換機能の使い方その１｜ Qiita

ソースデータフォーマットに従って、既定の桁数ずつに文字列を分割します。

「ALIAS」欄にも入力が必要ですが、手間な場合は ALIAS 欄先頭のアイコンクリックで自動入力も可能です。

さらにもう一つ Select コンポーネントを繋いで、「年」「月」「日」それぞれ2桁ずつ（１桁の場合は半角スペース）の文字列になっている項目を、Date 型１項目にまとめる処理を追加しました。

今回はソースデータのフォーマットを変換してロードしたかっただけなので Select コンポーネントだけで処理してみましたが、他にも Sort や Filter、Window 関数を使った処理ができるコンポーネントなど、いろいろなコンポーネントがあるそうで、データ分析用途に役立ちそうです。

Xplenty入門 Part3: 変換機能の使い方その２｜ Qiita

データロード先のコンポーネントを追加

最後に、データロード先となる「 Google BigQuery 」コンポーネントを追加します。

画面に配置したら前の Select コンポーネントとつなぎ、クリックして接続情報などを入力していきます。

conection を選択し、ロード先の BigQuery テーブル名とロードオプションを入力すると、データ項目とテーブルカラムを自動マッピングしてくれます。

Job 実行

package が作成できたので、画面上部の「Save」ボタンで保存した後、「Run job」ボタンで実行してみます。

「Choose a Cluster」で「New」ボタンをクリックし、クラスタ名を入力して「Sandbox」でクラスタを作成します。

「Choose a package」で先ほど作成した package が指定されていることを確認して「Next」

「Edit package variables」では package 実行時に使用する変数が指定できるそうですが、今回は不要なのでそのまま「Run job」

画面にポップアップが表示され、Job が実行されたことが確認できます。

「Jobs」画面から実行結果を確認してみます。

「FAILED」でした。。「View details」からエラーログをチェックします。

TypeCheckerException とのことで、どこかのデータ型がおかしいようです。。（ふむふむ、Xplenty は Java 実装で、luigi を使っているようですねｗ

package 画面上部のチェックマークの「Save and validate」ボタンでチェックしてみると、date 型に変換しようとしている２番目の Select コンポーネントでバリデーションエラーが出ています。。

チェック結果ウィンドウクリックでそのままエラーになっているコンポーネントの編集画面に飛ぶことができます。

date 型変換前の文字列を ＋ で結合していたことが原因でした。。CONCAT で結合するよう修正して再度チェックしてみると、今度は問題なさそうです。

もう一度 Job 実行してみます。

しばらく待っていると、ステータスが PENDING から RUNNING に変わり

あれ？また失敗した。。

エラーログ確認してみると、User does not have bigquery.jobs.create permission とのことで、BigQuery 側の権限設定が問題のようです。。

そういえば、、ドキュメントよく読まず、共有データセットに「BigQuery データ編集者」権限で Xplenty のサービスアカウントを追加しただけでした。。データ編集者だとジョブ実行権限はありませんね。。。

ドキュメントに倣ってプロジェクト閲覧者権限で IAM ユーザー追加してみたのですが変わらず。。（プロジェクト閲覧者にはやはり bigquery.jobs.create 権限ありませんよね。。

共有データセット側のロールを「BigQuery 管理者」に変更してみたのですが変わらず。。（bigquery.jobs.create 権限は付いたはずなのですが。。

IAM ユーザーにも bigquery.jobs.create 権限のある「BigQuery ジョブユーザー」ロールを追加して、ようやく正常終了できました。

GCP 管理コンソールからも確認してみます。

よしよし、ちゃんとテーブル作成できてますな。

が、あれ？データ入ってない。。。

ロード前のデータを確認するため、いったん S3 にファイル出力するように package を変更して再実行してみます。

正常終了を確認後、出力ファイルを確認してみると

zero,one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen,eighteen,nineteen,twenty,twentyOne,twentyTwo,twentyThree,yyyymmdd,locationCode,maxTime1,maxTide1,maxTime2,maxTide2,minTime1,minTide1,minTime2,minTide2
123,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
132,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
133,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
126,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

２番目以降の項目が何もありません。。（ SUBSTRING 使うと指定範囲外の文字列が切り捨てられて、次の項目では使えないのかな？

悩んだ結果、正規表現を駆使して、文字列を固定長ずつ分割するよう修正しました。

再実行してみます。

今度は無事、BigQuery に全てのデータをロードすることができました。

BigQuery のデータを参照

では、BigQuery の潮位データを参照して、釣り日和を確認してみます！

まずは明日の満潮時刻から。

明日の満潮時間は 00:34 と 11:50 とのことで、ちょうどお昼ごろですね。。（沖縄梅雨明けしたし、暑そうですなぁ。。

ちなみに来週土曜日（ 2020/06/20 ）は？

18:55 だったらまだ明るいし、涼しくなってそうだし、ちょうどいいかな？（今週はパスして来週にしようｗ

さらに、今年（2020 年）一番潮位が高くなる日は？

2020/09/19 ということは、ちょうど土曜日？！（釣りスケジュール登録しとこうｗ

まとめ（所感）

文字列の固定長分割の方法に少し手間取りましたが、一般的に分析基盤で使用するソースデータの CSV や Parquet などのファイルフォーマットであれば、プルダウン選択で自動的にカラムマッピングしてくれます。

また、今回は Select コンポーネントしか使用しませんでしたが、Select コンポーネントだけでも文字列操作などの関数が充実しているので、やりたいことが実現できました。他にもJOIN や WINDOW コンポーネントなど様々なコンポーネントがあるので、データ分析用途のデータ加工に便利そうです。

まったく初めてさわってみましたが、操作としては connection の作成と package の作成、Job の実行だけで、ドキュメントを読まなくても進めることができました。（ちょっとつまづいた時にドキュメントを探すくらいで大丈夫でした＞＜ｖ

Job 実行でエラーが発生したときなどはエラーログを確認する必要があるので、まったくプログラミングをやったことのない方には少しハードルが高いかなと感じましたが、package 作成などは本当に直感的に分かりやすいGUI 操作で完結できますし、サポートを活用すれば問題ないと思います。

さわってみて、実際に使いやすいツールだと思いました。